package com.fsnbigdata.repository;

import com.fsnbigdata.domain.Product;
import com.fsnbigdata.domain.dto.NamingPair;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import io.springlets.data.jpa.repository.DetachableJpaRepository;
import org.springframework.transaction.annotation.Transactional;

/**
 * = ProductRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = Product.class)
@Transactional(readOnly = true)
public interface ProductRepository extends DetachableJpaRepository<Product, Long>, ProductRepositoryCustom {

    List<NamingPair> findAllByInitial(String initial);
    
    List<Product> findAllByType(Integer type);

    List<Product> findAllByCategoryLike(String category);

    Page<Product> findAll(Specification<Product> spec, Pageable pageable);

    @Query(value = "select id,name,brand,initial,is_export,specification,category from product where if(?1 !='',type =?1,1=1) " +
            "AND if(?2 !='',brand like concat('%',?2,'%'),1=1) " +
            "AND if(?3 !='',category like concat('%',?3,'%'),1=1) " +
            "AND if(?4 !='',barCode like concat('%',?4,'%'),1=1)" +
            " AND if(?5 !='',name like concat('%',?5,'%'),1=1) " +
            "AND if(?6 !='',id in(?8),1=1) " +
            "AND if(?7 !='',id in(?9),1=1) " +
            "ORDER BY ?#{#pageable}",
            countQuery = "select count(*) from product where if(?1 !='',type =?1,1=1) " +
                    "AND if(?2 !='',brand like concat('%',?2,'%'),1=1) " +
                    "AND if(?3 !='',category like concat('%',?3,'%'),1=1) " +
                    "AND if(?4 !='',barCode like concat('%',?4,'%'),1=1)" +
                    " AND if(?5 !='',name like concat('%',?5,'%'),1=1) " +
                    "AND if(?6 !='',id in(?8),1=1) " +
                    "AND if(?7 !='',id in(?9),1=1) ",nativeQuery = true)
    List<Object> findAllByIdsAndTypeAndName(String type,String brand,String category,String barCode,
                                            String name,String sign,String sign1,List<Long> ids,
                                            List<Long> ids1,Pageable pageable);


    @Query(value = "select count(*) from product where if(?1 !='',type =?1,1=1) " +
            "AND if(?2 !='',brand like concat('%',?2,'%'),1=1) " +
            "AND if(?3 !='',category like concat('%',?3,'%'),1=1) " +
            "AND if(?4 !='',barCode like concat('%',?4,'%'),1=1)" +
            " AND if(?5 !='',name like concat('%',?5,'%'),1=1) " +
            "AND if(?6 !='',id in(?8),1=1) " +
            "AND if(?7 !='',id in(?9),1=1) ",nativeQuery = true)
    Long CountByKey(String type,String brand,String category,String barCode,
                    String name,String sign,String sign1,List<Long> ids,
                    List<Long> ids1);

    @Query(value = "select * from product",nativeQuery = true)
    List<Object> getCount();
}
